/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.ConnectionFactory;
import model.beans.Cliente;

/**
 *
 * @author Carol
 */
public class ClienteDAO {
    private Connection con;
    private PreparedStatement stmtInserir;
    private PreparedStatement stmtAlterar;
    private PreparedStatement stmtDeletar;
    private PreparedStatement stmtListar;
    private PreparedStatement stmtBuscar;
    
    public ClienteDAO() throws Exception{
        con = ConnectionFactory.getConnection();
    }
    
    public ClienteDAO(Connection con) throws Exception{
        this.con = con;        
    }
    
    public void inserir(Cliente cliente) throws SQLException{
       stmtInserir = con.prepareStatement("INSERT INTO cliente(nome, cpf, dtNasc, sexo, endereco, email) VALUES (?, ?, ?, ?, ?, ?);");
       stmtInserir.setString(1, cliente.getNome());
       stmtInserir.setString(2,cliente.getCpf());
       stmtInserir.setDate(3, new java.sql.Date(cliente.getDtNasc().getTime()));
       stmtInserir.setString(4, cliente.getSexo());
       stmtInserir.setString(5, cliente.getEndereco());
       stmtInserir.setString(6, cliente.getEmail());
       stmtInserir.executeUpdate();      
       stmtInserir.close();
    }
     
    public void alterar(Cliente cliente) throws SQLException{
       stmtAlterar = con.prepareStatement("UPDATE cliente SET nome = ? , cpf = ? , dtNasc = ? , sexo = ?, endereco = ?, email = ? WHERE idCliente = ? ;");
       stmtAlterar.setString(1, cliente.getNome());
       stmtAlterar.setString(2,cliente.getCpf());
       stmtAlterar.setDate(3, new java.sql.Date(cliente.getDtNasc().getTime()));
       stmtAlterar.setString(4, cliente.getSexo());
       stmtAlterar.setString(5, cliente.getEndereco());
       stmtAlterar.setString(6, cliente.getEmail());
       stmtAlterar.setInt(7,cliente.getId());
       stmtAlterar.executeUpdate();    
       stmtAlterar.close();
    }
    
    public void deletar(int id) throws SQLException{
        stmtDeletar = con.prepareStatement("DELETE FROM cliente WHERE idCliente = ? ;");
        stmtDeletar.setInt(1,id);
        stmtDeletar.executeUpdate();
        stmtDeletar.close();
    }
    
    public List<Cliente> listar() throws Exception{
        stmtListar = con.prepareStatement("SELECT idCliente, nome, cpf, dtNasc, sexo, endereco, email FROM cliente");
        ResultSet rs = stmtListar.executeQuery();
        List<Cliente> lista = new ArrayList();
        while(rs.next()){
            Cliente cliente = new Cliente();
            cliente.setId(rs.getInt("idCliente"));
            cliente.setNome(rs.getString("nome"));
            cliente.setCpf(rs.getString("cpf"));
            cliente.setDtNasc(rs.getDate("dtNasc"));
            cliente.setSexo(rs.getString("sexo"));
            cliente.setEndereco(rs.getString("endereco"));
            cliente.setEmail(rs.getString("email"));
            lista.add(cliente);
        }
        stmtListar.close();
        return lista;
    }
    
    public Cliente buscar(int id) throws Exception{
        stmtBuscar = con.prepareStatement("SELECT * FROM cliente WHERE idCliente = ? ;");
        Cliente cliente = new Cliente();
        stmtBuscar.setInt(1, id);
        ResultSet rs = stmtBuscar.executeQuery();
         if(rs.next()){
            cliente.setId(rs.getInt("idCliente"));
            cliente.setNome(rs.getString("nome"));
            cliente.setCpf(rs.getString("cpf"));
            cliente.setDtNasc(rs.getDate("dtNasc"));
            cliente.setSexo(rs.getString("sexo"));
            cliente.setEndereco(rs.getString("endereco"));
            cliente.setEmail(rs.getString("email"));
         }
        stmtBuscar.close();
        return cliente;
    }
}
